{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 打开已有的工作簿"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<openpyxl.workbook.workbook.Workbook at 0x24ab10cbc18>"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from openpyxl import load_workbook\n",
    "wb = load_workbook(r'C:\\Users\\zhangjunhong\\Desktop\\dataset.xlsx')\n",
    "wb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>岗位名称</th>\n",
       "      <th>行业</th>\n",
       "      <th>融资情况</th>\n",
       "      <th>公司规模</th>\n",
       "      <th>发布时间</th>\n",
       "      <th>工资</th>\n",
       "      <th>经验</th>\n",
       "      <th>学历</th>\n",
       "      <th>地址</th>\n",
       "      <th>招聘人title</th>\n",
       "      <th>职位简介</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ETL开发工程师、数据分析师</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000-9999人</td>\n",
       "      <td>发布于08月29日</td>\n",
       "      <td>8k-16k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>北京 海淀区 紫竹桥</td>\n",
       "      <td>招聘专员</td>\n",
       "      <td>[岗位职责：', '1、参与项目的ETL设计、开发、维护工作；', '2、参与ETL调度架构...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>数据分析师</td>\n",
       "      <td>数据服务</td>\n",
       "      <td>未融资</td>\n",
       "      <td>20-99人</td>\n",
       "      <td>发布于11月28日</td>\n",
       "      <td>8k-13k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>北京 朝阳区 朝外</td>\n",
       "      <td>HR</td>\n",
       "      <td>[职责描述：', '1. 为金融和政府行业客户开发数据分析解决方案，包括针对客户获取，信审决...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>数据分析师</td>\n",
       "      <td>互联网</td>\n",
       "      <td>未融资</td>\n",
       "      <td>100-499人</td>\n",
       "      <td>发布于10月25日</td>\n",
       "      <td>8k-13k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>北京 朝阳区 朝外</td>\n",
       "      <td>招聘主管</td>\n",
       "      <td>[任职资格： ', '1、 有2年以上数据处理和分析经验，具有p2p行业工作经验优先考虑； ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>数据分析师</td>\n",
       "      <td>未融资</td>\n",
       "      <td>未融资</td>\n",
       "      <td>20-99人</td>\n",
       "      <td>发布于11月28日</td>\n",
       "      <td>8k-13k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>北京 朝阳区 朝外</td>\n",
       "      <td>HR</td>\n",
       "      <td>[职责描述：', '1. 为金融和政府行业客户开发数据分析解决方案，包括针对客户获取，信审决...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>数据分析</td>\n",
       "      <td>不需要融资</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>发布于01月02日</td>\n",
       "      <td>8k-13k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>北京 昌平区 回龙观</td>\n",
       "      <td>招聘者</td>\n",
       "      <td>[岗位职责：', '1、负责完善数据报表体系，建立监控指标体系。关注平台日常运营数据报表，及...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233</th>\n",
       "      <td>数据分析</td>\n",
       "      <td>已上市</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>发布于昨天</td>\n",
       "      <td>10k-15k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>北京</td>\n",
       "      <td>招聘实习生</td>\n",
       "      <td>[职位描述：                                        ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>数据分析专员</td>\n",
       "      <td>未融资</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>发布于01月23日</td>\n",
       "      <td>10k-13k</td>\n",
       "      <td>1年以内</td>\n",
       "      <td>本科</td>\n",
       "      <td>北京 海淀区 西北旺</td>\n",
       "      <td>招聘主管</td>\n",
       "      <td>[项目介绍Project Introduction\\t主要发展方向为：报告自动化开发及可视化...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>人力资源数据分析师</td>\n",
       "      <td>互联网</td>\n",
       "      <td>不需要融资</td>\n",
       "      <td>1000-9999人</td>\n",
       "      <td>发布于昨天</td>\n",
       "      <td>10k-12k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>北京 海淀区 清河</td>\n",
       "      <td>招聘者</td>\n",
       "      <td>[岗位职责：', '1、对人力资源的各类数据进行处理和分析，将数据分析应用于重点人力资源项目...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>236</th>\n",
       "      <td>人力资源数据分析师</td>\n",
       "      <td>互联网</td>\n",
       "      <td>不需要融资</td>\n",
       "      <td>1000-9999人</td>\n",
       "      <td>发布于昨天</td>\n",
       "      <td>10k-12k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>北京 海淀区 清河</td>\n",
       "      <td>招聘主管</td>\n",
       "      <td>[岗位职责：', '1、对人力资源的各类数据进行处理和分析，将数据分析应用于重点人力资源项目...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>人力资源数据分析师</td>\n",
       "      <td>互联网</td>\n",
       "      <td>不需要融资</td>\n",
       "      <td>NaN</td>\n",
       "      <td>发布于昨天</td>\n",
       "      <td>10k-12k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>北京 海淀区 清河</td>\n",
       "      <td>招聘主管</td>\n",
       "      <td>[岗位职责：', '1、对人力资源的各类数据进行处理和分析，将数据分析应用于重点人力资源项目...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>238 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               岗位名称     行业   融资情况        公司规模       发布时间       工资    经验  学历  \\\n",
       "0    ETL开发工程师、数据分析师    NaN    NaN  1000-9999人  发布于08月29日   8k-16k  1-3年  本科   \n",
       "1             数据分析师   数据服务    未融资      20-99人  发布于11月28日   8k-13k  1-3年  本科   \n",
       "2             数据分析师    互联网    未融资    100-499人  发布于10月25日   8k-13k  1-3年  本科   \n",
       "3             数据分析师    未融资    未融资      20-99人  发布于11月28日   8k-13k  1-3年  本科   \n",
       "4              数据分析  不需要融资    NaN         NaN  发布于01月02日   8k-13k  1-3年  本科   \n",
       "..              ...    ...    ...         ...        ...      ...   ...  ..   \n",
       "233            数据分析    已上市    NaN         NaN      发布于昨天  10k-15k  1-3年  本科   \n",
       "234          数据分析专员    未融资    NaN         NaN  发布于01月23日  10k-13k  1年以内  本科   \n",
       "235       人力资源数据分析师    互联网  不需要融资  1000-9999人      发布于昨天  10k-12k  1-3年  本科   \n",
       "236       人力资源数据分析师    互联网  不需要融资  1000-9999人      发布于昨天  10k-12k  1-3年  本科   \n",
       "237       人力资源数据分析师    互联网  不需要融资         NaN      发布于昨天  10k-12k  1-3年  本科   \n",
       "\n",
       "             地址 招聘人title                                               职位简介  \n",
       "0    北京 海淀区 紫竹桥     招聘专员  [岗位职责：', '1、参与项目的ETL设计、开发、维护工作；', '2、参与ETL调度架构...  \n",
       "1     北京 朝阳区 朝外       HR  [职责描述：', '1. 为金融和政府行业客户开发数据分析解决方案，包括针对客户获取，信审决...  \n",
       "2     北京 朝阳区 朝外     招聘主管  [任职资格： ', '1、 有2年以上数据处理和分析经验，具有p2p行业工作经验优先考虑； ...  \n",
       "3     北京 朝阳区 朝外       HR  [职责描述：', '1. 为金融和政府行业客户开发数据分析解决方案，包括针对客户获取，信审决...  \n",
       "4    北京 昌平区 回龙观      招聘者  [岗位职责：', '1、负责完善数据报表体系，建立监控指标体系。关注平台日常运营数据报表，及...  \n",
       "..          ...      ...                                                ...  \n",
       "233          北京    招聘实习生  [职位描述：                                        ...  \n",
       "234  北京 海淀区 西北旺     招聘主管  [项目介绍Project Introduction\\t主要发展方向为：报告自动化开发及可视化...  \n",
       "235   北京 海淀区 清河      招聘者  [岗位职责：', '1、对人力资源的各类数据进行处理和分析，将数据分析应用于重点人力资源项目...  \n",
       "236   北京 海淀区 清河     招聘主管  [岗位职责：', '1、对人力资源的各类数据进行处理和分析，将数据分析应用于重点人力资源项目...  \n",
       "237   北京 海淀区 清河     招聘主管  [岗位职责：', '1、对人力资源的各类数据进行处理和分析，将数据分析应用于重点人力资源项目...  \n",
       "\n",
       "[238 rows x 11 columns]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(r'C:\\Users\\zhangjunhong\\Desktop\\dataset.xlsx')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>岗位名称</th>\n",
       "      <th>行业</th>\n",
       "      <th>融资情况</th>\n",
       "      <th>公司规模</th>\n",
       "      <th>发布时间</th>\n",
       "      <th>工资</th>\n",
       "      <th>经验</th>\n",
       "      <th>学历</th>\n",
       "      <th>地址</th>\n",
       "      <th>招聘人title</th>\n",
       "      <th>职位简介</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>高级数据分析师</td>\n",
       "      <td>互联网</td>\n",
       "      <td>未融资</td>\n",
       "      <td>100-499人</td>\n",
       "      <td>发布于11月05日</td>\n",
       "      <td>15k-20k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>深圳 南山区 科技园</td>\n",
       "      <td>产品总监</td>\n",
       "      <td>[岗位职责：', '1.负责公司业务日常运营数据的监控，提供专业的数据分析报告；', '2....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>数据分析师</td>\n",
       "      <td>互联网</td>\n",
       "      <td>D轮及以上</td>\n",
       "      <td>1000-9999人</td>\n",
       "      <td>发布于11月26日</td>\n",
       "      <td>10k-15k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>深圳 福田区 梅林</td>\n",
       "      <td>招聘者</td>\n",
       "      <td>[工作职责：', '1. 负责用户行为、司机管理活动运营、业务数据等分析，在产品和运营两个方...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>数据分析 - 创新工具</td>\n",
       "      <td>移动互联网</td>\n",
       "      <td>D轮及以上</td>\n",
       "      <td>10000人以上</td>\n",
       "      <td>发布于01月16日</td>\n",
       "      <td>11k-20k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>深圳 南山区 南油</td>\n",
       "      <td>火山审核编辑</td>\n",
       "      <td>[职位描述', '1、负责业务指标体系建设；', '2、负责日常业务运营数据分析，多维度/多...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>数据分析师</td>\n",
       "      <td>数据服务</td>\n",
       "      <td>C轮</td>\n",
       "      <td>100-499人</td>\n",
       "      <td>发布于01月07日</td>\n",
       "      <td>20k-30k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>深圳 福田区 岗厦</td>\n",
       "      <td>HRBP</td>\n",
       "      <td>[1.负责公司客户样本数据清洗、入库；', '2.负责公司数据产品回溯测试；', '3.产品...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>数据分析</td>\n",
       "      <td>互联网</td>\n",
       "      <td>已上市</td>\n",
       "      <td>10000人以上</td>\n",
       "      <td>发布于01月04日</td>\n",
       "      <td>15k-30k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>本科</td>\n",
       "      <td>深圳 南山区 科技园</td>\n",
       "      <td>HR</td>\n",
       "      <td>[岗位内容：', '1、负责项目运维相关数据的指标系统建设，在对业务深入全面了解的基础上，梳...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>数据分析师</td>\n",
       "      <td>移动互联网</td>\n",
       "      <td>不需要融资</td>\n",
       "      <td>10000人以上</td>\n",
       "      <td>发布于06月25日</td>\n",
       "      <td>20k-40k</td>\n",
       "      <td>经验不限</td>\n",
       "      <td>学历不限</td>\n",
       "      <td>深圳 福田区 梅林</td>\n",
       "      <td>大数据经理</td>\n",
       "      <td>[岗位职责：', '1. 负责CPD、广告、推荐等相关项目数据分析、专题分析及策略算法策划、...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>数据分析</td>\n",
       "      <td>计算机软件</td>\n",
       "      <td>不需要融资</td>\n",
       "      <td>10000人以上</td>\n",
       "      <td>发布于08月13日</td>\n",
       "      <td>20k-21k</td>\n",
       "      <td>5-10年</td>\n",
       "      <td>本科</td>\n",
       "      <td>深圳 龙岗区 坂田</td>\n",
       "      <td>数据分析师</td>\n",
       "      <td>[AI解决方案工程师（BA）', '岗位职责', '1、负责公司AI大数据产品的数据分析、需...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>数据分析师</td>\n",
       "      <td>电子商务</td>\n",
       "      <td>已上市</td>\n",
       "      <td>1000-9999人</td>\n",
       "      <td>发布于12月24日</td>\n",
       "      <td>4k-7k</td>\n",
       "      <td>经验不限</td>\n",
       "      <td>本科</td>\n",
       "      <td>深圳 龙岗区 坂田</td>\n",
       "      <td>HR</td>\n",
       "      <td>[岗位职责', '1、负责协助数据同事完成用研工作；', '2、负责基础数据导出、有简单的数...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>数据分析</td>\n",
       "      <td>生活服务</td>\n",
       "      <td>已上市</td>\n",
       "      <td>10000人以上</td>\n",
       "      <td>发布于01月30日</td>\n",
       "      <td>7k-10k</td>\n",
       "      <td>3-5年</td>\n",
       "      <td>本科</td>\n",
       "      <td>深圳</td>\n",
       "      <td>招聘主管</td>\n",
       "      <td>[岗位职能：', '1.负责店铺整体数据的挖掘整理包含：商品、品类、店铺、竞品、行业大盘等；...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>行业数据分析师</td>\n",
       "      <td>移动互联网</td>\n",
       "      <td>不需要融资</td>\n",
       "      <td>500-999人</td>\n",
       "      <td>发布于02月15日</td>\n",
       "      <td>12k-18k</td>\n",
       "      <td>1-3年</td>\n",
       "      <td>硕士</td>\n",
       "      <td>深圳 南山区 科技园</td>\n",
       "      <td>HR</td>\n",
       "      <td>[岗位职责：', '1. 行业数据分析挖掘。针对电子政务及相关领域的数据应用进行深度研究，根...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>184 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            岗位名称     行业   融资情况        公司规模       发布时间       工资     经验    学历  \\\n",
       "0        高级数据分析师    互联网    未融资    100-499人  发布于11月05日  15k-20k   1-3年    本科   \n",
       "1          数据分析师    互联网  D轮及以上  1000-9999人  发布于11月26日  10k-15k   1-3年    本科   \n",
       "2    数据分析 - 创新工具  移动互联网  D轮及以上    10000人以上  发布于01月16日  11k-20k   1-3年    本科   \n",
       "3          数据分析师   数据服务     C轮    100-499人  发布于01月07日  20k-30k   1-3年    本科   \n",
       "4           数据分析    互联网    已上市    10000人以上  发布于01月04日  15k-30k   1-3年    本科   \n",
       "..           ...    ...    ...         ...        ...      ...    ...   ...   \n",
       "179        数据分析师  移动互联网  不需要融资    10000人以上  发布于06月25日  20k-40k   经验不限  学历不限   \n",
       "180         数据分析  计算机软件  不需要融资    10000人以上  发布于08月13日  20k-21k  5-10年    本科   \n",
       "181        数据分析师   电子商务    已上市  1000-9999人  发布于12月24日    4k-7k   经验不限    本科   \n",
       "182         数据分析   生活服务    已上市    10000人以上  发布于01月30日   7k-10k   3-5年    本科   \n",
       "183      行业数据分析师  移动互联网  不需要融资    500-999人  发布于02月15日  12k-18k   1-3年    硕士   \n",
       "\n",
       "             地址 招聘人title                                               职位简介  \n",
       "0    深圳 南山区 科技园     产品总监  [岗位职责：', '1.负责公司业务日常运营数据的监控，提供专业的数据分析报告；', '2....  \n",
       "1     深圳 福田区 梅林      招聘者  [工作职责：', '1. 负责用户行为、司机管理活动运营、业务数据等分析，在产品和运营两个方...  \n",
       "2     深圳 南山区 南油   火山审核编辑  [职位描述', '1、负责业务指标体系建设；', '2、负责日常业务运营数据分析，多维度/多...  \n",
       "3     深圳 福田区 岗厦     HRBP  [1.负责公司客户样本数据清洗、入库；', '2.负责公司数据产品回溯测试；', '3.产品...  \n",
       "4    深圳 南山区 科技园       HR  [岗位内容：', '1、负责项目运维相关数据的指标系统建设，在对业务深入全面了解的基础上，梳...  \n",
       "..          ...      ...                                                ...  \n",
       "179   深圳 福田区 梅林    大数据经理  [岗位职责：', '1. 负责CPD、广告、推荐等相关项目数据分析、专题分析及策略算法策划、...  \n",
       "180   深圳 龙岗区 坂田    数据分析师  [AI解决方案工程师（BA）', '岗位职责', '1、负责公司AI大数据产品的数据分析、需...  \n",
       "181   深圳 龙岗区 坂田       HR  [岗位职责', '1、负责协助数据同事完成用研工作；', '2、负责基础数据导出、有简单的数...  \n",
       "182          深圳     招聘主管  [岗位职能：', '1.负责店铺整体数据的挖掘整理包含：商品、品类、店铺、竞品、行业大盘等；...  \n",
       "183  深圳 南山区 科技园       HR  [岗位职责：', '1. 行业数据分析挖掘。针对电子政务及相关领域的数据应用进行深度研究，根...  \n",
       "\n",
       "[184 rows x 11 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(r'C:\\Users\\zhangjunhong\\Desktop\\dataset.xlsx',sheet_name = '深圳')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 创建新的工作簿"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "wb = Workbook()\n",
    "\n",
    "wb.save(r\"C:\\Users\\zhangjunhong\\Desktop\\工作簿1.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-06-15T06:16:49.463408Z",
     "start_time": "2019-06-15T06:16:44.789694Z"
    }
   },
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "wb = Workbook()\n",
    "\n",
    "ws = wb.active \n",
    "\n",
    "ws[\"A1\"] = 1#给单元格A1赋值1\n",
    "ws[\"A2\"] = 2#给单元格A2赋值2\n",
    "ws[\"A3\"] = 3#给单元格A3赋值3\n",
    "\n",
    "wb.save(r\"C:\\Users\\zhangjunhong\\Desktop\\cell_file.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "wb = Workbook()\n",
    "\n",
    "ws = wb.active #默认情况下，在创建wb的时候就会新建一个sheet，可以使用这行代码激活\n",
    "\n",
    "ws.cell(row = 1,column = 1).value = 1#给第1行第1列赋值1\n",
    "ws.cell(row = 2,column = 1).value = 2#给第2行第1列赋值2\n",
    "ws.cell(row = 3,column = 1).value = 3#给第3行第1列赋值3\n",
    "\n",
    "wb.save(r\"C:\\Users\\zhangjunhong\\Desktop\\cell_file_copy.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "wb = Workbook()\n",
    "\n",
    "ws = wb.active #默认情况下，在创建wb的时候就会新建一个sheet，可以使用这行代码激活\n",
    "\n",
    "ws.append([1,2,3,4,5])#给某一行单元格赋值\n",
    "\n",
    "wb.save(r\"C:\\Users\\zhangjunhong\\Desktop\\cell_row.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "wb = Workbook()\n",
    "\n",
    "ws = wb.active #默认情况下，在创建wb的时候就会新建一个sheet，可以使用这行代码激活\n",
    "\n",
    "#循环插入多行数据\n",
    "data = [\n",
    "    [\"Fruit\", 2011, 2012, 2013, 2014],\n",
    "    ['Apples', 10000, 5000, 8000, 6000],\n",
    "    ['Pears',  2000, 3000, 4000, 5000],\n",
    "    ['Bananas', 6000, 6000, 6500, 6000],\n",
    "    ['Oranges',  500,  300,  200,  700],\n",
    "]\n",
    "\n",
    "for row in data:\n",
    "    ws.append(row)\n",
    "\n",
    "wb.save(r\"C:\\Users\\zhangjunhong\\Desktop\\cell_for.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas 与 openpyxl 之间的转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#导入df\n",
    "import pandas as pd\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.utils.dataframe import dataframe_to_rows\n",
    "\n",
    "df = pd.read_excel(r\"C:\\Users\\zhangjunhong\\Desktop\\pandas_style.xlsx\")\n",
    "\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "for r in dataframe_to_rows(df,index = False,header = True):\n",
    "    ws.append(r)\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\openpyx_style.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户id</th>\n",
       "      <th>浏览日期</th>\n",
       "      <th>加购物车日期</th>\n",
       "      <th>成交日期</th>\n",
       "      <th>浏览月</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3654005</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>20184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3664955</td>\n",
       "      <td>2018-04-11</td>\n",
       "      <td>2018-04-11</td>\n",
       "      <td>2018-04-11</td>\n",
       "      <td>20184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3665888</td>\n",
       "      <td>2018-04-04</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>2018-04-12</td>\n",
       "      <td>20184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3666143</td>\n",
       "      <td>2018-04-12</td>\n",
       "      <td>2018-04-12</td>\n",
       "      <td>2018-04-12</td>\n",
       "      <td>20184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3673112</td>\n",
       "      <td>2018-04-16</td>\n",
       "      <td>2018-04-16</td>\n",
       "      <td>2018-04-16</td>\n",
       "      <td>20184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>3652500</td>\n",
       "      <td>2018-04-02</td>\n",
       "      <td>2018-04-02</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>20184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>3652501</td>\n",
       "      <td>2018-04-04</td>\n",
       "      <td>2018-04-04</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>20184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>3652502</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>20184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>3652506</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>20184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>3652513</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>20184</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       用户id       浏览日期     加购物车日期       成交日期    浏览月\n",
       "0   3654005 2018-04-05 2018-04-05 2018-04-05  20184\n",
       "1   3664955 2018-04-11 2018-04-11 2018-04-11  20184\n",
       "2   3665888 2018-04-04 2018-04-05 2018-04-12  20184\n",
       "3   3666143 2018-04-12 2018-04-12 2018-04-12  20184\n",
       "4   3673112 2018-04-16 2018-04-16 2018-04-16  20184\n",
       "..      ...        ...        ...        ...    ...\n",
       "94  3652500 2018-04-02 2018-04-02 2018-04-05  20184\n",
       "95  3652501 2018-04-04 2018-04-04 2018-04-05  20184\n",
       "96  3652502 2018-04-05 2018-04-05 2018-04-05  20184\n",
       "97  3652506 2018-04-05 2018-04-05 2018-04-05  20184\n",
       "98  3652513 2018-04-05 2018-04-05 2018-04-05  20184\n",
       "\n",
       "[99 rows x 5 columns]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sheet相关设置"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 新建一个Sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "wb = Workbook()\n",
    "\n",
    "ws = wb.active \n",
    "\n",
    "ws1 = wb.create_sheet()\n",
    "\n",
    "wb.save(r\"C:\\Users\\zhangjunhong\\Desktop\\new_sheet.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "wb = Workbook()\n",
    "\n",
    "ws = wb.active \n",
    "\n",
    "ws1 = wb.create_sheet()#新建的第 1 个 Sheet\n",
    "ws2 = wb.create_sheet()#新建的第 2 个 Sheet\n",
    "ws3 = wb.create_sheet()#新建的第 3 个 Sheet\n",
    "\n",
    "wb.save(r\"C:\\Users\\zhangjunhong\\Desktop\\more_new_sheet.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "wb = Workbook()\n",
    "\n",
    "ws = wb.active\n",
    "\n",
    "ws1 = wb.create_sheet()\n",
    "ws2 = wb.create_sheet()\n",
    "\n",
    "# '第二 sheet'表示 Sheet 名\n",
    "# 1 表示新建在第一个 Sheet 后面\n",
    "ws3 = wb.create_sheet('第二 Sheet',1)\n",
    "\n",
    "wb.save(r\"C:\\Users\\zhangjunhong\\Desktop\\sort_new_sheet.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "wb = Workbook()\n",
    "\n",
    "ws = wb.active\n",
    "\n",
    "ws1 = wb.create_sheet()\n",
    "ws2 = wb.create_sheet()\n",
    "ws3 = wb.copy_worksheet(ws2)\n",
    "\n",
    "wb.save(r\"C:\\Users\\zhangjunhong\\Desktop\\copy_new_sheet.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 对已有 Sheet 进行设置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sheet', 'Sheet1', 'Sheet2', 'Sheet2 Copy']"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from openpyxl import load_workbook\n",
    "wb = load_workbook(r'C:\\Users\\zhangjunhong\\Desktop\\copy_new_sheet.xlsx')\n",
    "wb.sheetnames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "wb = Workbook()\n",
    "\n",
    "ws = wb.active \n",
    "\n",
    "ws.title = \"New Title\" #更改sheet的名称\n",
    "ws.sheet_properties.tabColor = \"FFEE0000\" #更改sheet的标签颜色\n",
    "\n",
    "wb.save(r\"C:\\Users\\zhangjunhong\\Desktop\\New_Title.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "wb = Workbook()\n",
    "\n",
    "ws = wb.active \n",
    "\n",
    "ws1 = wb.create_sheet()\n",
    "ws2 = wb.create_sheet()\n",
    "ws3 = wb.create_sheet()\n",
    "\n",
    "wb.remove(ws2) #将w2删除\n",
    "\n",
    "wb.save(r\"C:\\Users\\zhangjunhong\\Desktop\\remove_new_sheet.xlsx\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "789px",
    "left": "69px",
    "top": "110px",
    "width": "308.514px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
